package com.codingman.springbootdemo7;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Optional;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.stereotype.Repository;

@Repository
public class EmployeeDao {
	@Autowired
	JdbcTemplate jdbcTemplate;

	public Boolean save(Employee e) {
		String query = "insert into employee(name,password,email,sex,country) values (?,?,?,?,?)";

		return jdbcTemplate.execute(query, new PreparedStatementCallback<Boolean>() {
			@Override
			public Boolean doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
				ps.setString(1, e.getName());
				ps.setString(2, e.getPassword());
				ps.setString(3, e.getEmail());
				ps.setString(4, e.getSex());
				ps.setString(5, e.getCountry());

				return ps.execute();
			}
		});
	}

	public boolean update(Employee e) {
		String query = "update employee set name=?,password=?,email=?,sex=?,country=? where id=?";
		Object[] params = new Object[] { e.getName(), e.getPassword(), e.getEmail(), e.getSex(), e.getCountry(),
				e.getId() };
		return jdbcTemplate.update(query, params) == 1;
	}

	public boolean delete(int id) {
		String query = "delete from employee where id=?";
		Object[] params = new Object[]{id};
		return jdbcTemplate.update(query, params) == 1;
	}
/*
 * Optional类主要解决的问题是臭名昭著的空指针异常(NullPointerException) 
 */
	public Optional<Employee> getEmployeeById(int id) {
		String query = "select * from employee where id=?";
		return Optional.of(jdbcTemplate.queryForObject(query, new BeanPropertyRowMapper<Employee>(Employee.class), id));
	}

	public List<Employee> getAllEmployees() {
		String query = "select * from employee";
		return jdbcTemplate.query(query, new BeanPropertyRowMapper<Employee>(Employee.class));
	}
}
